<?php
namespace Admin\Controller;
use Admin\Controller\BaseController;
class StatisticsController extends BaseController {

    public function base(){
        //用户总数，迷笛先生总数，考官总数，音频下载总数，视频收藏总数
        $user_count = M('user')->where('user_display = 1')->count();
        $mrmidi_count = M('user')->where('user_display = 1 and user_role in (2,3)')->count();
        $examiner_count = M('user')->where('user_display = 1 and user_role in (3,4)')->count();
        $audio_download_count = M('audio')->where('audio_display = 1')->sum('audio_download_count');
        $video_collect_count = M('video')->where('video_display = 1')->sum('video_collect_count');
        $order_totalprice = M('order')->where('order_paystatus in (2,3)')->sum('order_totalprice');
        $statistics = array();
        $statistics['user_count'] = $user_count;
        $statistics['mrmidi_count'] = $mrmidi_count;
        $statistics['examiner_count'] = $examiner_count;
        $statistics['audio_download_count'] = $audio_download_count;
        $statistics['video_collect_count'] = $video_collect_count;
        $statistics['order_totalprice'] = $order_totalprice;
        $this->assign('statistics',$statistics);
        $this->display();
    }

	public function index() {
        $data = array_filter($_GET);
        if (!empty($_GET['user_name'])) {
            $data['user_name'] = array('like',"%".trim($_GET['user_name'])."%");
        }
        unset($data['p']);
        $data['user_role'] = array('IN','3,4');
        $data['user_display'] = 1;
        $count = M('user')->field('user_id,user_name')->where($data)->count();
        $page = getpage($count,10);
        $examiner_arr = M('user')->field('user_id,user_name')->where($data)->limit($page->firstRow, $page->listRows)->select();
        $exam_arr = array();
        foreach ($examiner_arr as $k => $v) {
            $exam_count = M('exam')->where('exam_examiner_id = '.$v['user_id'])->count('exam_id');
            $v['exam_count'] = $exam_count;
            $exam_finish_count = M('exam')->where('exam_status = 3 and exam_examiner_id = '.$v['user_id'])->count('exam_id');
            $v['exam_finish_count'] = $exam_finish_count;
            $exam_income = M('exam')->where('exam_status = 3 and exam_examiner_id = '.$v['user_id'])->sum('exam_income');
            $v['exam_income'] = $exam_income;
            array_push($exam_arr, $v);
        }
        $this->assign('exam_arr', $exam_arr);
        $this->assign('page', $page->show());
        $this->display();
    }

    public function statistics_details(){

        $user_id = $_GET['user_id'];
        $month_first = date('Y-m-01',time());
        //考官基本信息
        $user_info = M('user')->field('user_id,user_name,user_phone')->where('user_id = '.$user_id)->find();
        $this->assign('user_info',$user_info);
        //本月
        $time['exam_endtime'] = array('EGT',$month_first);
        $this_month = $this->examInfo($user_id,$time);
        $this->assign('this_month',$this_month);
        //上个月
        $bone_month = date('Y-m-01',strtotime('-1 month'));
        $time['exam_endtime'] = array('BETWEEN',array($bone_month,$month_first));
        $one_month = $this->examInfo($user_id,$time);
        $this->assign('one_month',$one_month);
        //最近三个月
        $bthree_month = date('Y-m-01',strtotime('-3 month'));
        $time['exam_endtime'] = array('BETWEEN',array($bthree_month,$month_first));
        $three_month = $this->examInfo($user_id,$time);
        $this->assign('three_month',$three_month);
        //最近半年
        $bsix_month = date('Y-m-01',strtotime('-6 month'));
        $time['exam_endtime'] = array('BETWEEN',array($bsix_month,$month_first));
        $six_month = $this->examInfo($user_id,$time);
        $this->assign('six_month',$six_month);
        //所有监考的考试列表
        $count = M('exam')->field('exam_income,exam_endtime')->where('exam_examiner_id = '.$user_id)->count();
        $page = getpage($count,10);
        $examiner_arr = M('user')->field('user_id,user_name')->where($data)->limit($page->firstRow, $page->listRows)->select();
        $exam_arr = M('exam')->field('exam_id,exam_major_id,exam_level,exam_result,exam_income,exam_endtime')->where('exam_examiner_id = '.$user_id)->order('exam_id desc')->limit($page->firstRow, $page->listRows)->select();
        $this->assign('exam_arr',$exam_arr);
        $this->assign('page', $page->show());
        $this->display();
    }

    public function examInfo($user_id,$time){
        $exam_count = M('exam')->where('exam_examiner_id = '.$user_id)->where($time)->count('exam_id');
        $exam_info['exam_count'] = $exam_count;
        $exam_finish_count = M('exam')->where('exam_status = 3 and exam_examiner_id = '.$user_id)->where($time)->count('exam_id');
        $exam_info['exam_finish_count'] = $exam_finish_count;
        $exam_income = M('exam')->where('exam_status = 3 and exam_examiner_id = '.$user_id)->where($time)->sum('exam_income');
        $exam_info['exam_income'] = $exam_income;
        return $exam_info;
    }

    public function exam_details(){
        $data['exam_id'] = I('get.exam_id');
        $exam_arr = M('exam')->field('e.*,u.user_name,u.user_phone,m.*,t.*')->alias('e')->join('left join midi_user as u on e.exam_user_id = u.user_id')->join('left join midi_midiclub as m on e.exam_midiclub_id = m.midiclub_id')->join('left join midi_examtime as t on e.exam_time = t.examtime_id')->where($data)->find();
        $this->assign('exam_arr',$exam_arr);
        $this->display();
    }

    /**
    * 数组转xls格式的excel文件
    * @param  array  $data      需要生成excel文件的数组
    * @param  string $filename  生成的excel文件名
    *      示例数据：
    *       $data = array(
    *          array(NULL, 2010, 2011, 2012),
    *           array('Q1',   12,   15,   21),
    *           array('Q2',   56,   73,   86),
    *           array('Q3',   52,   61,   69),
    *           array('Q4',   30,   32,    0),
    *          );
    */
    public function statistics_export(){
        $where['exam_examiner_id'] = I('get.examiner_id');
        $exam_arr = M('exam')->field('e.*,u.user_name,u.user_phone,t.*')->alias('e')->join('left join midi_user as u on e.exam_user_id = u.user_id')->join('left join midi_examtime as t on e.exam_time = t.examtime_id')->where($where)->order('e.exam_endtime desc')->select();
        $data = array();
        $title = array('序号','考试ID','考试编号','专业','等级','考试时间','考点','考生姓名','考生手机号码','考试状态','考官收入','结束时间');
        array_push($data, $title);
        if (empty($exam_arr)) {
            $this->error('该考官没有监考记录');
            exit();
        }
        foreach ($exam_arr as $k => $v) {
            //考试ID
            $exam_id = $v['exam_id'];
            //考试编号
            $exam_num = $v['exam_num'];
            //考试专业
            if ($v['exam_major_id'] == 1) {
                $exam_major = '电吉他';
            }elseif ($v['exam_major_id'] == 2) {
                $exam_major = '爵士鼓';
            }elseif ($v['exam_major_id'] == 3) {
                $exam_major = '电贝司';
            }
            //考试等级
            $exam_level = $v['exam_level'];
            //考试时间
            $exam_time = $v['exam_ymd'].' '.$v['examtime_time'];
            //考点
            if ($v['exam_midiclub_id'] == 111111) {
                $exam_address = '线上';
            }else{
                $exam_address = '线下';
            }
            //考生姓名
            $user_name = $v['user_name'];
            //考生手机号码
            $user_phone = $v['user_phone'];
            //考试状态
            if ($v['exam_status'] == 1) {
                $exam_status = '报名成功';
            }elseif ($v['exam_status'] == 2) {
                $exam_status = '已指派老师';
            }elseif ($v['exam_status'] == 3) {
                $exam_status = '已完成考试';
            }
            //考官收入
            $exam_income = $v['exam_income'];
            //结束时间
            $exam_endtime = $v['exam_endtime'];
            $arr = array();
            $arr = array(($k+1),$exam_id,$exam_num,$exam_major,$exam_level,$exam_time,$exam_address,$user_name,$user_phone,$exam_status,$exam_income,$exam_endtime);
            array_push($data, $arr);
        }
        $filename = date('YmdHis',time()).rand(1000,9999).'.xls';
        create_xls($data,$filename);
    }

    public function goods() {
        $month_first = strtotime(date('Y-m-01',time()));
        $condition['order_paystatus'] = array('IN','2,3');
        //本月
        $condition['order_createtime'] = array('EGT',$month_first);
        $this_month['count'] = M('order')->where($condition)->count();
        $this_month['sum'] = M('order')->where($condition)->sum('order_totalprice');
        if (empty($this_month['sum'])) { $this_month['sum'] = 0; }
        $this->assign('this_month',$this_month);
        //上个月
        $bone_month = strtotime(date('Y-m-01',strtotime('-1 month')));
        $condition['order_createtime'] = array(array('EGT',$bone_month),array('LT',$month_first));
        $one_month['count'] = M('order')->where($condition)->count();
        $one_month['sum'] = M('order')->where($condition)->sum('order_totalprice');
        if (empty($one_month['sum'])) { $one_month['sum'] = 0; }
        $this->assign('one_month',$one_month);
        //最近三个月
        $bthree_month = strtotime(date('Y-m-01',strtotime('-3 month')));
        $condition['order_createtime'] = array(array('EGT',$bthree_month),array('LT',$month_first));
        $three_month['count'] = M('order')->where($condition)->count();
        $three_month['sum'] = M('order')->where($condition)->sum('order_totalprice');
        if (empty($three_month['sum'])) { $three_month['sum'] = 0; }
        $this->assign('three_month',$three_month);
        //最近半年
        $bsix_month = strtotime(date('Y-m-01',strtotime('-6 month')));
        $condition['order_createtime'] = array(array('EGT',$bsix_month),array('LT',$month_first));
        $six_month['count'] = M('order')->where($condition)->count();
        $six_month['sum'] = M('order')->where($condition)->sum('order_totalprice');
        if (empty($six_month['sum'])) { $six_month['sum'] = 0; }
        $this->assign('six_month',$six_month);
        $this->display();
    }

}